#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:do_sqlalchemy2.py
# author:Jackiex
# datetime:2023/5/18 14:23
# software: PyCharm

"""
this is function  description 
"""

from sqlalchemy import create_engine,desc


from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.schema import FetchedValue
from sqlalchemy.orm import sessionmaker,declarative_base

Base = declarative_base()

engine = create_engine("mysql+pymysql://test:123456@127.0.0.1:3306/sct_study",  encoding="UTF-8")


class Student(Base):
    __tablename__ = 'student'

    StudentID = Column(String(20, 'utf8mb4_0900_ai_ci'), primary_key=True, info='学号')
    Name = Column(String(20, 'utf8mb4_0900_ai_ci'), nullable=False, info='姓名')
    Sex = Column(String(10, 'utf8mb4_0900_ai_ci'), nullable=False, info='性别')
    EnrollmentTime = Column(DateTime, info='入学时间')
    DepartmentID = Column(Integer, info='所在院系')
    ClassID = Column(String(20, 'utf8_general_ci'), info='所在班级')
    Address = Column(String(255, 'utf8mb4_0900_ai_ci'), info='家庭住址')
    AddTime = Column(DateTime, server_default=FetchedValue(), info='记录添加时间')


# 把当前的引擎绑定给这个会话
Session = sessionmaker(bind=engine)

# 实例化
session = Session()

'''
   1 数据库记录添加
'''

'''创建一个要添加的学生对象'''
student = Student(
    StudentID='112110125487',
    Name='tom',
    Sex='tom',
    EnrollmentTime='2022-11-30',
    DepartmentID=2,
    ClassID='950102',
    Address='北京市昌平区回龙观龙腾苑12-1-1',
)

# 数据库操作--数据添加
session.add(student)

# 操作数据提交
session.commit()

# 关闭session
session.close()

# 打印提示语句
print("添加记录成功")

'''
    2 数据库记录查询
'''

''' 不带条件的查询 '''
'''2.1 all() 全量查询'''
students = session.query(Student).all()

# 此时students是映射类的对象列表
for student in students:
    # student 是映射类的一个对象
    print(student.__dict__)

'''2.2 first() 首个记录'''
student = session.query(Student).first()
print(student.__dict__)

'''2.3 limit() 限制记录返回数量'''
# # 获取前两条数据
students = session.query(Student).limit(2).all()
for student in students:
    print(student.__dict__)



'''2.4 offset() 设置偏移量'''
# 从第三条记录开始获取数据
students = session.query(Student).offset(2).all()
for student in students:
    print(student.__dict__)

'''2.5 结合limit与offset'''
# 从第三条记录开始获取两条数据
students = session.query(Student).offset(2).limit(2).all()
for student in students:
    print(student.__dict__)

'''2.6 order_by()排序'''
# # 默认顺序
students = session.query(Student).order_by(Student.EnrollmentTime).all()
for student in students:
    print(student.__dict__)

# desc 倒序
students = session.query(Student).order_by(desc(Student.EnrollmentTime)).all()
for student in students:
    print(student.__dict__)

''' 带条件的查询 '''
'''查询男生记录'''
# filter过滤条件任意
students = session.query(Student).filter(Student.Sex == '男').all()
for student in students:
    print(student.__dict__)

# filter_by 只能等值过滤
students = session.query(Student).filter_by(Sex='男').all()
for student in students:
    print(student.__dict__)

'''多个条件：查询DepartmentID为2的女生信息'''
students = session.query(Student).filter(Student.Sex == '女', Student.DepartmentID == '2').all()
for student in students:
    print(student.__dict__)

'''查询姓张的同学的信息'''
students = session.query(Student).filter(Student.Name.like('张%')).all()
for student in students:
    print(student.__dict__)

'''选择条件'''
from sqlalchemy import or_, and_, all_, any_

students = session.query(Student).filter(or_(Student.Sex == '男', Student.ClassID == '95315')).all()
for student in students:
    print(student.__dict__)

students = session.query(Student).filter(and_(Student.Sex == '男', Student.ClassID == '95315')).all()
for student in students:
    print(student.__dict__)


'''
   3  数据库记录修改
'''

'''3.1 修改单条数据'''
# # 数据库操作--数据修改
student = session.query(Student).filter_by(StudentID='112110125487').first()

# 修改student属性
student.Name = 'Tonny'
student.Sex = 'female'

# 操作数据提交
session.commit()

# 关闭session
session.close()

print('修改记录成功')

'''3.2 修改多条数据 将 ClassID(95312) 的学生 置入 ClassID(95315)班'''
# 数据库操作--数据修改
session.query(Student).filter_by(ClassID='95312').update({'ClassID': '95315'})

# 操作数据提交
session.commit()

# 关闭session
session.close()

print('修改记录成功')

'''
   4  数据库记录删除
'''

'''4.1 删除单条数据'''
# # 数据库操作 -- 数据删除
session.query(Student).filter_by(StudentID='112110125487').delete()

# 操作数据提交
session.commit()

# 关闭session
session.close()

print("删除记录成功")

'''4.2 删除所有记录'''
session.query(Student).delete()

# 操作数据提交
session.commit()

# 关闭session
session.close()
print('所有记录删除成功')
